package com.shuai.sql;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.shuai.stickynote.MemoryNote;
import com.shuai.stickynote.PlantNote;
import com.shuai.stickynote.TaskNote;
import com.shuai.stickynote.TextNote;

import java.util.ArrayList;

public class SQL {
    private String TAG = "fuck";
    private Helper helper;

    public SQL(Context context) {
        helper = new Helper(context,"my.db",null,2);
    }

    public class Helper extends SQLiteOpenHelper{

        public Helper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sql = "create table if not exists stickynote(id text primary key, title text, time text, type text, content text, mgroup text, complete integer, mdelete integer)";
            String sql2 = "create table if not exists memory(id text primary key, time text, ch text, en text, mgroup text, complete integer, mdelete integer)";
            String sql3 = "create table if not exists plant(id text primary key, cTime text, day text, content text, title text, mgroup text, mdelete integer, complete integer)";
            String sql4 = "create table if not exists task(id text primary key, title text, content text, mgroup text, time text, tagtype text, complete integer, mdelete integer)";
            db.execSQL(sql);
            db.execSQL(sql2);
            db.execSQL(sql3);
            db.execSQL(sql4);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            String sql = "drop table if exists stickynote";
            db.execSQL(sql);
            String sql2 = "drop table if exists memory";
            db.execSQL(sql2);
            String sql3 = "drop table if exists plant";
            db.execSQL(sql3);
            String sql4 = "drop table if exists task";
            db.execSQL(sql4);
        }
    }
//    text相关
//    id, title, time, type, content, mgroup, complete, mdelete
    public void insertSticky(TextNote sticky){
        SQLiteDatabase db = helper.getWritableDatabase();
        String id,title,time,type,content,group,complete,delete;
        id = sticky.getId();
        title = sticky.getTitle();
        time = sticky.getTime();
        type = sticky.getType();
        content = sticky.getContent();
        group = sticky.getGroup();
        complete = String.valueOf(sticky.getComplete());
        delete = String.valueOf(sticky.getDelete());
        String sql = "insert into stickynote values(?,?,?,?,?,?,?,?)";
        db.execSQL(sql,new String[]{id,title,time,type,content,group,complete,delete});
    }
    public ArrayList<TextNote> selectTextByAll(){
        ArrayList<TextNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from stickynote where type = ?";
        Cursor cursor = db.rawQuery(sql,new String[]{"text"});
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String title = cursor.getString(1);
            String time = cursor.getString(2);
            String type = cursor.getString(3);
            String content = cursor.getString(4);
            String group = cursor.getString(5);
            int complete = cursor.getInt(6);
            int delete = cursor.getInt(7);
            TextNote sticky = new TextNote(id,time,type,content,title,group,complete,delete);
            list.add(sticky);
        }
        cursor.close();
        return list;
    }
    public TextNote selectTextById(String key){
        SQLiteDatabase db = helper.getReadableDatabase();
        if (key.equals("")) return null;
        TextNote sticky = null;
        String sql = "select * from stickynote where type = ? and id = ?";
        Cursor cursor = db.rawQuery(sql,new String[]{"text",key});
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String title = cursor.getString(1);
            String time = cursor.getString(2);
            String type = cursor.getString(3);
            String content = cursor.getString(4);
            String group = cursor.getString(5);
            int complete = cursor.getInt(6);
            int delete = cursor.getInt(7);
            sticky = new TextNote(id,time,type,content,title,group,complete,delete);
        }
        cursor.close();
        return sticky;
    }
    public void updateTextById(TextNote sticky){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "update stickynote set time = ? , title = ?, content = ?, complete = ? where id = ?";
        db.execSQL(sql,new String[]{sticky.getTime(),sticky.getTitle(),
                sticky.getContent(),String.valueOf(sticky.getComplete()),sticky.getId()});
    }
    public void deleteStickyById(String key){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "delete from stickynote where id = ?";
        db.execSQL(sql,new String[]{key});
    }
//    plant相关
//    id, cTime, day, content, title, mgroup, mdelete, complete
    public void insertPlant(PlantNote plantNote){
        SQLiteDatabase db = helper.getWritableDatabase();
        String id, cTime, day, content, title, group, delete,complete;
        id = plantNote.getId();
        cTime = plantNote.getcTime();
        day = plantNote.getDay();
        content = plantNote.getContent();
        title = plantNote.getTitle();
        group = plantNote.getGroup();
        delete = String.valueOf(plantNote.getDelete());
        complete = String.valueOf(plantNote.getComplete());
        String sql = "insert into plant values(?,?,?,?,?,?,?,?)";
        db.execSQL(sql,new String[]{id, cTime, day, content, title, group, delete, complete});
    }
    public ArrayList<PlantNote> selectPlantByAll(){
        ArrayList<PlantNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from plant";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String cTime = cursor.getString(1);
            String day = cursor.getString(2);
            String content = cursor.getString(3);
            String title = cursor.getString(4);
            String group = cursor.getString(5);
            int delete = cursor.getInt(6);
            int complete = cursor.getInt(7);
            PlantNote plant = new PlantNote(id,cTime,day,content,title,group,delete,complete);
            list.add(plant);
        }
        cursor.close();
        return list;
    }
    public ArrayList<PlantNote> selectPlantByDoing(){
        ArrayList<PlantNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from plant";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String cTime = cursor.getString(1);
            String day = cursor.getString(2);
            String content = cursor.getString(3);
            String title = cursor.getString(4);
            String group = cursor.getString(5);
            int delete = cursor.getInt(6);
            int complete = cursor.getInt(7);
            PlantNote plant = new PlantNote(id,cTime,day,content,title,group,delete,complete);
            if (plant.getComplete() == 0) list.add(plant);
        }
        cursor.close();
        return list;
    }
    public ArrayList<PlantNote> selectPlantByBefort(){
        ArrayList<PlantNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from plant";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String cTime = cursor.getString(1);
            String day = cursor.getString(2);
            String content = cursor.getString(3);
            String title = cursor.getString(4);
            String group = cursor.getString(5);
            int delete = cursor.getInt(6);
            int complete = cursor.getInt(7);
            PlantNote plant = new PlantNote(id,cTime,day,content,title,group,delete,complete);
            if (plant.getComplete() == 1) list.add(plant);
        }
        cursor.close();
        return list;
    }
    public ArrayList selectPlantByDay(String day){
        ArrayList<PlantNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from plant where day = ?";
        Cursor cursor = db.rawQuery(sql,new String[]{day});
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String cTime = cursor.getString(1);
            String mday = cursor.getString(2);
            String content = cursor.getString(3);
            String title = cursor.getString(4);
            String group = cursor.getString(5);
            int delete = cursor.getInt(6);
            int complete = cursor.getInt(7);
            PlantNote plant = new PlantNote(id,cTime,mday,content,title,group,delete,complete);
            list.add(plant);
        }
        cursor.close();
        return list;
    }
    public void updatePlant(PlantNote plant){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "update plant set title = ?, content = ?, mdelete = ?, complete = ? where id = ?";
        db.execSQL(sql,new String[]{plant.getTitle(),plant.getContent(),
                String.valueOf(plant.getDelete()),String.valueOf(plant.getComplete()),plant.getId()});
    }
    public void deletePlantById(String id){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "delete from plant where id = ?";
        db.execSQL(sql,new String[]{id});
    }
//    task相关
//    id, title, content, group, time, tagtype, complete, mdelete
    public void insertTask(TaskNote task){
        SQLiteDatabase db = helper.getWritableDatabase();
        String id,title,time,content,group,complete,delete,tagType;
        id = task.getId();
        title = task.getTitle().equals("") ? "我的目标" : task.getTitle();
        time = task.getTime();
        content = task.getContent();
        group = task.getGroup();
        tagType = task.getTagType();
        complete = String.valueOf(task.getComplete());
        delete = String.valueOf(task.getDelete());
        String sql = "insert into task values(?,?,?,?,?,?,?,?)";
        db.execSQL(sql,new String[]{id,title,content,group,time,tagType,complete,delete});
    }
    public void insertTask(ArrayList<TaskNote> tasks){
        for (TaskNote task : tasks) {
            insertTask(task);
        }
    }
    public ArrayList selectTaskAllGroup(){
        ArrayList<String> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from task group by mgroup";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String group = cursor.getString(3);
            list.add(group);
        }
        cursor.close();
        return list;
    }
    public ArrayList selectTaskByGroup(String group){
        ArrayList<TaskNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from task where mgroup = ?";
        Cursor cursor = db.rawQuery(sql,new String[]{group});
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String title = cursor.getString(1);
            String content = cursor.getString(2);
            String mgroup = cursor.getString(3);
            String time = cursor.getString(4);
            String tagType = cursor.getString(5);
            int complete = cursor.getInt(6);
            int delete = cursor.getInt(7);
            TaskNote sticky = new TaskNote(id,title,content,mgroup,time,tagType,complete,delete);
            list.add(sticky);
        }
        cursor.close();
        return list;
    }
    public ArrayList selectTaskByAllSorted(){
        ArrayList<String> groups = selectTaskAllGroup();
        ArrayList<ArrayList<TaskNote>> lists = new ArrayList<>();
        for (String group:groups) {
            lists.add(selectTaskByGroup(group));
        }
        return lists;
    }
    public void updateTask(TaskNote task){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "update task set title = ?, content = ?, tagtype = ?, complete = ?, mdelete = ? where id = ?";
        db.execSQL(sql,new String[]{task.getTitle(),task.getContent(),task.getTagType(),
                String.valueOf(task.getComplete()),String.valueOf(task.getDelete()),task.getId()});
    }
    public void updateTask(ArrayList<TaskNote> tasks){
        for (TaskNote task : tasks) {
            updateTask(task);
        }
    }
    public void deleteTask(String id){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "delete from task where id = ?";
        db.execSQL(sql,new String[]{id});
    }
    public void deleteTask(ArrayList<String> ids){
        for (String id : ids) {
            deleteTask(id);
        }
    }
    public void deleteTaskByGroup(String group){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "delete from task where mgroup = ?";
        db.execSQL(sql,new String[]{group});
    }
//    memory相关
//    id, time, ch, en, mgroup, complete, mdelete
    public void insertMemory(MemoryNote memory){
        SQLiteDatabase db = helper.getWritableDatabase();
        String id,time,ch,en,group,complete,delete;
        id = memory.getId();
        time = memory.getTime();
        ch = memory.getCh();
        en = memory.getEn();
        group = memory.getGroup();
        complete = String.valueOf(memory.getComplete());
        delete = String.valueOf(memory.getDelete());
        String sql = "insert into memory values(?,?,?,?,?,?,?)";
        db.execSQL(sql,new String[]{id,time,ch,en,group,complete,delete});
    }
    public void insertMemory(ArrayList<MemoryNote> memorys){
        for (MemoryNote memory : memorys) {
            insertMemory(memory);
        }
    }
    public ArrayList selectMemoryAllGroup(){
        ArrayList<String> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from memory group by mgroup";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String group = cursor.getString(4);
            list.add(group);
        }
        cursor.close();
        return list;
    }
    public ArrayList selectMemoryByGroup(String group){
        ArrayList<MemoryNote> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select * from memory where mgroup = ?";
        Cursor cursor = db.rawQuery(sql,new String[]{group});
        while (cursor.moveToNext()){
            String id = cursor.getString(0);
            String time = cursor.getString(1);
            String ch = cursor.getString(2);
            String en = cursor.getString(3);
            String mgroup = cursor.getString(4);
            int complete = cursor.getInt(5);
            int delete = cursor.getInt(6);
            MemoryNote memory = new MemoryNote(id,en,ch,mgroup,time,complete,delete);
            list.add(memory);
        }
        cursor.close();
        return list;
    }
    public ArrayList selectMemoryByAllSorted(){
        ArrayList<String> groups = selectMemoryAllGroup();
        ArrayList<ArrayList<MemoryNote>> lists = new ArrayList<>();
        for (String group:groups) {
            lists.add(selectMemoryByGroup(group));
        }
        return lists;
    }
    public void updateMemory(MemoryNote memory){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "update memory set ch = ?, en = ?, complete = ?, mdelete = ? where id = ?";
        db.execSQL(sql,new String[]{memory.getCh(),memory.getEn(),String.valueOf(memory.getComplete()),
                            String.valueOf(memory.getDelete()),memory.getId()});
    }
    public void updateMemory(ArrayList<MemoryNote> memorys){
        for (MemoryNote memory : memorys) {
            updateMemory(memory);
        }
    }
    public void deleteMemory(String id){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "delete from memory where id = ?";
        db.execSQL(sql,new String[]{id});
    }
    public void deleteMemory(ArrayList<String> ids){
        for (String id : ids) {
            deleteMemory(id);
        }
    }
    public void deleteMemoryByGroup(String group){
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "delete from memory where mgroup = ?";
        db.execSQL(sql,new String[]{group});
    }

}
